<%@ Page Language="VB" MasterPageFile="~/masterpages/dev.master" AutoEventWireup="false" CodeFile="datamanager.aspx.vb" Inherits="dev_datamanager" title="Untitled Page" %>
<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">
    <h2>Data Abstraction Theory</h2>
    <p>
        This is bad code:
    </p>
<pre>// [default.aspx.cs]
// selecting records from a database
using System.Data.SqlClient;
using System.Configuration;

...

namespace MyWebsite
{
    public class MyWebpage : System.Web.UI.Page
    {
        protected void Page_Load()
        {
            if (!Page.IsPostBack)
            {
                // bind to gridview
                SqlCommand comm = new SqlCommand();
                SqlDataReader reader;
                comm.Connection = new SqlConnection(
                        ConfigurationManager.ConnectionStrings("localdns").ConnectionString
                        );
                comm.Connection.Open();
                comm.CommandText = "SELECT ID, Username, Posts, Signature FROM myTable where ID = @id";
                comm.Parameters.AddWithValue("@id", Request.QueryString("ID"));
                
                reader = comm.ExecuteReader();
                GridView1.DataSource = reader;
                GridView1.DataBind();
                
                reader.Close();
                comm.Connection.Close();
            }
        }
    }
}
</pre>
<p>
    The code above is written perfectly fine, but its design makes it a maintanence hell. If a 
    programmer changes the name of a column in the database, for example changinge "Posts"
    to "PostCount", then the code above will compile without error; but it'll throw an exception
    at runtime, because the column "Posts" isn't found in the table. In fact, with a modification
    like that, a programmer needs to change every single sql query and gridview in 
    the entire project which uses the "Posts" column to accomodate the changes.
</p>
<p>
    Of course, there are more subtle problems too. If instead of changing the name of a column,
    what happens if a programmer changes a column's datatype? Perhaps the programmers wants to 
    change a bit field to an integer field... a change like that has unpredictable effects.
    And once again, those effects are not discovered until runtime.
</p>
<p>
    Data-driven applications are extremely error prone for two reasons:
</p>
<ol type="1">
    <li>
        Column names, column datatypes / nullibility, table names, connection strings can change
        without notice. Code will compile no matter what the database looks like, but it will 
        throw runtime errors. In the worst case, it doesn't throw a runtime error, but 
        you get unexpected results.
    </li>
    <li>
        <p>
        Results in a dataset are weakly typed. When we need to store those results in another
        variable, we have to cast a field to our desired datatype. For example:
        </p>
<pre>int userID = (int)reader["ID"];          // if the "ID" field is an Int64 instead of an
                                         // int, then I'm fucked until I change this line of code.
</pre>
        <p>
        A lot of programming effort is wasted when you have to cast a field to a datatype every
        time you want to use it. Even worse, if that datatype changes, the code compiles, but
        will almost certainly throw a runtime exception. You're forced to change every cast
        in your program... and if your field is nullable, you have to add that check as well.
        </p>
    </li>
</ol>
<p>
    Little things like that make database-driven application development a nightmare.
</p>
<h2>Solution To Nightmare</h2>
<p>
    As programmers, we are lazy at heart. We never want to write the same code twice, simply 
    because we know a change in one place will necessitate a change in any duplicate code. 
    As we can see from the simple example above, traditional data-access methods force us
    edit our entire application everytime we make a change to the database, so the traditional
    method is unacceptable
</p>
<p>
    To resolve the maintanence nightmare, we need to abstract away our data access methods. 
    Our abstract data access framework should have two properties:
</p>
<ol type="1">
    <li>
        <p>
        All of our sql queries behind the scenes, so that the programmer can ignore the details of the database and focus only on the application logic.
        </p>
    </li>
    <li>
        <p>
        Accessing fields in a record should be type-safe.
        </p>
    </li>
</ol>

<h2>Typesafe Record And Field Design</h2>
<p>
    We usually write code like this:
</p>
<pre>int userID = (int)reader["ID"];
</pre>
<p>    
    because we prefer to work with the <code>userID</code> variable. A typed variable is more 
    convenient to use because operations on it are predictable, and mispelling the variable name
    results in a compilation error instead of a runtime error. We don't have to reference our
    datareader anymore when we want to use the ID field.
</p>
<p>
    If we can do that with one field, why not do it to the whole record? Why not encapsulate
    all the fields in a record with a special "record" class?
</p>
<p>
    Let say you have a table like this:
</p>
    <table width="90%" border="1" align="center">
        <tr>
            <th width="10%">PK</th>
            <th width="40%">Field</th>
            <th width="40%">Type</th>
            <th width="10%">Nullable</th>
        </tr>
        <tr>
            <td><input type="checkbox" checked="checked" disabled="disabled" /></td>
            <td>ID</td>
            <td>int</td>
            <td><input type="checkbox" disabled="disabled" /></td>
        </tr>
        <tr>
            <td><input type="checkbox" disabled="disabled" /></td>
            <td>Username</td>
            <td>Varchar(25)</td>
            <td><input type="checkbox" disabled="disabled" /></td>
        </tr>
        <tr>
            <td><input type="checkbox" disabled="disabled" /></td>
            <td>Password</td>
            <td>Char(32)</td>
            <td><input type="checkbox" disabled="disabled" /></td>
        </tr>
        <tr>
            <td><input type="checkbox" disabled="disabled" /></td>
            <td>PostCount</td>
            <td>int</td>
            <td><input type="checkbox" disabled="disabled" /></td>
        </tr>
        <tr>
            <td><input type="checkbox" disabled="disabled" /></td>
            <td>Signature</td>
            <td>Varchar(max)</td>
            <td><input type="checkbox" checked="checked" disabled="disabled" /></td>
        </tr>
        <tr>
            <td><input type="checkbox" disabled="disabled" /></td>
            <td>DateRegistered</td>
            <td>SmallDateTime</td>
            <td><input type="checkbox" disabled="disabled" /></td>
        </tr>
        <tr>
            <td><input type="checkbox" disabled="disabled" /></td>
            <td>Gender</td>
            <td>char(1)</td>
            <td><input type="checkbox" disabled="disabled" /></td>
        </tr>
        <tr>
            <td><input type="checkbox" disabled="disabled" /></td>
            <td>Birthday</td>
            <td>Smalldatetime</td>
            <td><input type="checkbox" checked="checked" disabled="disabled" /></td>
        </tr>
        <tr>
            <td><input type="checkbox" disabled="disabled" /></td>
            <td>AccountVerified</td>
            <td>bit</td>
            <td><input type="checkbox" disabled="disabled" /></td>
        </tr>
    </table>
<p>
    You can represent every field in the table with a class like this:
</p>
<pre>namespace DataAccess
{
    public class User
    {
        private int _id;
        pubilc int ID
        {
            get {return _id;}
            set {_id = value;}
        }
        
        private string _username;
        public string Username
        {
            get {return _username;}
            set {_username = value;}
        }
        
        private string _password;
        public string Password
        {
            get {return _password;}
            set {_password = value;
        }
        
        private int _postcount;
        public int PostCount
        {
            get {return _postcount;}
            set {_postcount = value;}
        }
        
        private string _signature;
        public string Signature
        {
            get {return _signature;}
            set {_signature = value;}
        }
        
        private DateTime _registered;
        Public DateTime DateRegistered
        {
            get {return _registered;}
            set {_registered = value;}
        }
        
        
        public enum Genders
        {
            MALE
            FEMALE
            ANON
        }
        
        private Genders _sex;
        public Genders Sex
        {
            get {return _sex;}
            set {_sex = value;}
        }
        
        private Nullable&lt;DateTime&gt; _birthday;
        public Nullable&lt;DateTime&gt; Birthday
        {
            get
            {
                if (_birthday.HasValue)
                {
                    return _birthday.Value;
                }
                else
                {
                    return null;
                }
            }
            set {_birthday = value;}
        }
        
        private bool _accountverified;
        public bool AccountVerified
        {
            get {return _accountverified;}
            set {_accountverified = value;}
        }
    }
}
</pre>
<p>
    Notice how I accomadate nullable fields. Strings can be initialized to null, but DateTimes
    are not; the Birthday field is nullable in the database, so I represent in the class using
    a <a href="http://msdn2.microsoft.com/en-us/library/1t3y8s4s(VS.80).aspx">nullable type</a>.
    (I could have used 
    <code>DateTime?</code>, but I prefer the more explicit 
    <code>Nullable&lt;DateTime&gt;</code>.)
</p>
<p>
    I also represent the Gender field with an enum instead of string, for a few reasons: (1) I
    want to constrain input to a few predefined values, and (2) I want to work with discrete, 
    intuitive values rather than <a href="http://c2.com/cgi/wiki?MagicNumber">magic numbers
    and strings</a>.
</p>
<p>
    An abstract data access framework can construct all of the the records for you.
    As long as the framework initializes the properties in the User class correctly,
    you can use the values a typesafe fashion:
</p>
<pre>
/*
    This code represents hypothetical usage.
*/

// code to request a user with UserID = 2 from data access framework
myUser = GetUserByID(2);

Console.WriteLine(myUser.Username); // prints "Yahweh"
Console.WriteLine(myUser.PostCount); // prints 66600

myUser.PostCount = 120;
Console.WriteLine(myUser.PostCount); // prints 120

/*
myUser.PostCount = "cats";  // doesn't compile
Console.WriteLine(myUser.PostCount);
*/

myUser.Save(); // commits changes to the database
</pre>

<h2>Tables And Records</h2>
<p>
    The abstract data access framework is responsible
    for initializing the properties of the user class, so the framework needs to know the name 
    of the field and columns to pull its information. The User class represents a record in the
    users table; so, we need a seperate class to represent the User table in the database. This 
    new class, called <code>Users</code> will contain the information needed to initialize the 
    fields in the User class. (Note the design scheme: "Users" plural is a table. "User" is a 
    record.)
</p>
<p>
    Our Users class needs to know the name of our table; it needs to know how to copy records 
    from the table to a User object; and it needs to know how to copy properties from a User 
    object back to the table.
</p>
<p>
    We might imagine our Users table like this:
</p>
<pre>
namespace DataAccess
{
    public class Users
    {
        public string Table
        {
            get {return "Users";}
        }
        
        protected void CopyFromTable(DataRow Row, User Item)
        {
            Item.Username = (string)Row["Username"];
            Item.AccountVerified = Convert.ToBoolean(Row["AccountVerified"]);
            // ... and so on for each property of Item
        }
        protected void CopyToTable(DataRow Row, User Item)
        {
            Row["Username"] = Item.Username;
            Row["AccountVerified"] = Item.AccountVerified;
            // ... and so on
        }
    }
}
</pre>
<p>
    Now, if a column Users table changes, you only need to make the corresponding changes
    in the Users class <code>CopyFromTable</code> and <code>CopyToTable</code> methods (which
    are used by the data access framework). No 
    further changes are necessary to fix the remainder of your application.
</p>
<p>
    We can add functionality to our Users class too. The Users class should be able to talk 
    directly to the data access framework; the framework has a few useful methods:<br />
    - <code>GetItems();</code>, which returns typed a <code>List&lt;&gt;</code> of items in a 
    recordset. In the case of the Users class, it will return a <code>List&lt;User&gt;</code>.<br />
    - <code>GetItem(long);</code>, which returns a single item from a database, corresponding to 
    unique ID of the item.<br />
    -  <code>AddParam(string name, object value)</code> which allows you filter results in a dataset
    (it is implemented as a where clause in a query)
</p>
<pre>
namespace DataAccess
{
    public class Users
    {
        public string Table
        {get {return "Users";}}
        
        protected void CopyFromTable(DataRow Row, User Item)
        { ... }
        protected void CopyToTable(DataRow Row, User Item)
        { ... }
        
        public User Item(long id)
        {
            return GetItem(id);
        }
        
        public List&lt;User&gt; All()
        {
            return GetItems();
        }
        
        public List&lt;User&gt; GetByUsername(string username)
        {
            AddParam("Username", username);
            return GetItems();
        }
        
        public User GetByUsernameAndPassword(string username, password)
        {
            // mimics a "login"
            
            AddParam("Username", username);
            AddParam("Password", password);
            List&gt;User&gt; myUsers = GetItems();
            if (myUsers.Count == 0) // no users found
            {
                return null;
            }
            else    // user authenticated
            {
                return myUsers[0];  // first item in list
            }
        }
    }
}
</pre>

<p>
Now you can retrieve instances of users very easily:
</p>
<pre>    public void Login(string username, string password)
    {
        myUser = new Users.GetByUsernameAndPassword(username, password);
        if (myUser != null)
        {
            // do stuff
        }
        else
        {
            // bad username or password. Prompt user to try again.
        }
    }
</pre>

<p>
Databinding with User class is easy because it inherits from IEnumerable. If you had a page
that displayed a user's profile, you could use something like this:
</p>
<pre>[profile.aspx]
&lt;asp:GridView runat="server" ID="grdProfile" AutoGenerateColumns="False" &gt;
    &lt;Columns&gt;
        &lt;asp:TemplateField&gt;
            &lt;p&gt;Username: &lt;%# Eval("Username") %&gt;&lt;/p&gt;
            &lt;p&gt;Post count: &lt;%# Eval("Username") %&gt;&lt;/p&gt;
        &lt;/asp:TemplateField&gt;
    &lt;/Columns&gt;
&lt;/GridView&gt;


[profile.aspx.cs]
protected void Page_Load()
{
    if (!Page.IsPostBack)
    {
        long userID = Convert.ToInt64(Request.Querystring("ID"));
        grdProfile.DataSource = new Users.Item(userID);
        grdProfile.DataBind();
    }
}
</pre>
<p>
    Its not a lot of work to setup the data access classes. Your code can become very succinct,
    especially if you convert the <code>Item</code>, <code>All</code>, <code>GetByUsername</code>,
    and any other methods in your Users class to static methods. As a programmer, you don't 
    have to write queries anymore; the datamanager will do it all for you.
</p>

<h2>Fuck Joins</h2>
<p>
    90% of the time, tables have a relationship with one another. If you have messageboard, 
    you might have a table containing posts, and the posts table will contain a foreign key to
    the ID field in a the users table. Anytime you want to retrieve posts and a username, you
    would normally write an SQL statement with a join:
</p>
<pre>    SELECT * FROM Posts
    INNER JOIN Users on (Users.ID = Posts.UserID)
</pre>
<p>
    Using an abstract data access framework, you'll never need to use joins. Ever. The advantage 
    of a data access framework is that you can reference users from the Posts table directly,
    provided you design your classes appropriately.
</p>
<p>
    Here are some simplified User and Post classes:
</p>
<pre>namespace DataAccess
{
    public class User
    {
        private string _username;
        public string Username
        {
            get { return _username; }
            set {_username = value;}
        }
        
        private int _postcount;
        Public int PostCount
        {
            get {return _postcound;}
            set {_postcount = value;}
        }
    }
    
    public class Post
    {
        private string _subject;
        public string Subject
        {
            get {return _subject;}
            set {_subject = value;}
        }
        
        private string _post;
        public string Post
        {
            get {return _post;}
            set {_post = value;}
        }
        
        private User _user;
        public User User
        {
            get {return _user;}
            set {_user = value;}
        }
    }
}</pre>

<p>
My Users class stays the same, but in my Posts class, I have something like this:
</p>
<pre>namespace Posts
{
    public string Table {get {return "Posts";} }
    protected void CopyFromTable(DataRow Row, Post Item)
    {
        Item.Subject = (string)Row["Subject"];
        Item.Post = (string)Row["Post"];
        Item.User = Users.Item(Convert.ToInt64(Row["UserID"]));
    }
    
    protected void CopyToTable(DataRow Row, Post Item)
    {
        Row["Subject"] = Item.Subject;
        Row["Post"] = Item.Post;
        Row["UserID"] = Item.User.ID;
    }
}
</pre>

<p>
Now my users table is directly connected to my posts table. If I want to see who wrote 
the post #666, I can execute <code>Posts.Item(666).User.Username;</code>.
</p>

<p>
    Yes, its possible to write large, complicated applications without using a single join
    anywhere in the code.
</p>

<h2>Performance Boosts</h2>
<p>
    A  line of code like <code>Posts.Item(666).User.Username</code> execute two queries against the
    database; one for the Post, and one for the User.
    Its perfectly reasonable to ask whether a data access framework performs well. 
</p>
<p>
    I've written very complicated bank software using a similar abstract framework, and I
    haven't had any performance issues. Performance is addressed in 2 ways:
</p>
<h3>Object Pooling</h3>
<p>
    After the framework creates an instance of the user
    class, the instance is stored in an 
    <a href="http://www.sturmnet.org/blog/archives/2005/09/08/object-pooling/">object pool</a>.
    An object pool is essentially a hash table containing all instances of a class that have 
    been previously created.
</p>
<p>
    The benefits of object pooling in a data access framework are substantial for two reasons:
</p>
<ol type="1">
   <li>
        <p>
            I execute fewer queries against the database. If I need to retrieve the same user
            twice, I do one query to retrieve my user, store the user in a pool, then reuse the
            user everytime afterward.
        </p>
    </li>
    <li>
        <p>
            Fewer objects created. It takes time for the an application to construct an instance
            of a class; the time is usually trivial for a few objects, but there can be noticeable
            delays if a single action creates 1000s of objects. Instead of recreating the same
            user object over and over again, I create the use once, and I retrieve the user from
            the pool everytime afterward.
        </p>
   </li>
</ol>
<p>
    As an interesting consequence of object pooling, as I make more and more requests to the
    database, the object pool contains more and more items; my application becomes faster 
    when I execute more queries.
</p>

<h3>SQL Server Is Smart</h3>
<p>
    SQL Server is a smart database engine. When you execute a query against the database, it
    creates an execution plan, then it caches the execution plan for similar queries. A complicated
    query can take 20 seconds to execute; but the execution time will drop substantially after
    the first couple runs of the query (it might take 5 or 10 seconds).
</p>
<p>
    In addition to that, SQL server is pretty smart about executing multiple queries: 
    connections to the database are pooled by default. A typical web page might execute
    20 or more queries (which isn't uncommon on DotNetNuke and PHPNuke websites), and still 
    display in a few tenths of a second.
</p>
</asp:Content>

